﻿Public Class stored_functions
    Private _db As hc_databaseDataContext
    Private _alternativstring As String


    Public Function VonProjektZuProjektteam(ByVal id As String) As String
        Dim _db As hc_databaseDataContext
        _db = New hc_databaseDataContext
        Dim var = From it In _db.Arbeitet_ans _
                  Where it.ProjektID = id _
                  And it.Rolle = "Teammitglied" _
                  Select it.Mitgliede_Anwaerter
        Dim s As String = ""

        For Each Mitgliede_Anwaerter In var
            If s = "" Then
                s = Mitgliede_Anwaerter.Vorname + " " + Mitgliede_Anwaerter.Nachname
            Else
                s = s + ", " + Mitgliede_Anwaerter.Vorname + " " + Mitgliede_Anwaerter.Nachname

            End If

        Next
        Return s
    End Function

    


    Public Function VonProjektZuPM(ByVal id As Integer)
        Dim _db As hc_databaseDataContext = New hc_databaseDataContext

        Try
            Dim var = (From it In _db.Arbeitet_ans _
                      Where it.ProjektID = id And it.Rolle = "PM" _
                      Select it.Mitgliede_Anwaerter).Single

            Return var.Vorname + " " + var.Nachname
        Catch
            Return _alternativstring
        End Try


    End Function

    Public Function GibAlleProjektTeilnehmer(ByVal id As Integer)

        Dim var = From it In _db.Arbeitet_ans _
                  Where it.ProjektID = id _
                    Select it.Mitgliede_Anwaerter


        Return var



    End Function
    Public Function stringInVorundNachnameZerlegen(ByVal strtext As String) As ArrayList


        Dim strTeile As ArrayList = New ArrayList

        Dim intLetzte As Integer = 0
        Dim j As Integer = 0

        For i = 0 To strtext.Length - 1
            If strtext.Substring(i, 1) = " " Then

                strTeile.Add(strtext.Substring(intLetzte, i - intLetzte))
                Dim test As String = strTeile(0)
                strTeile.Add(strtext.Substring(i - intLetzte + 1, (strtext.Length - test.Length) - 1))
                j += 1
                intLetzte = i + 1
            End If
        Next

        Return strTeile
    End Function

    Public Function VonProjektzuJpm(ByVal id As Integer)
        Dim _db As hc_databaseDataContext = New hc_databaseDataContext
        Try
            Dim var = (From it In _db.Arbeitet_ans _
                      Where it.ProjektID = id And it.Rolle = "JPM" _
                      Select it.Mitgliede_Anwaerter).Single

            Return var.Vorname + " " + var.Nachname

        Catch
            Return _alternativstring
        End Try

    End Function

    Public Function VonProjektZuPL(ByVal id As Integer)
        Dim _db As hc_databaseDataContext = New hc_databaseDataContext
        Try
            Dim var = (From it In _db.Arbeitet_ans _
                      Where it.ProjektID = id And it.Rolle = "PL" _
                      Select it.Mitgliede_Anwaerter).Single

            Return var.Vorname + " " + var.Nachname
        Catch
            Return _alternativstring
        End Try

    End Function

    Public Function vonProjektzuLetzterStand(ByVal id As Integer)
        Try
            Dim var = (From it In _db.Stands _
                      Where it.Projekt_ID = id _
                      Select it.Projektstandbesch).Last

            Return var

        Catch
            Return _alternativstring
        End Try


    End Function
    ' hier muss noch etwas getan werden -> Reportings durch angebote PT
    Public Function vonProjektzuLetzterStandProzent(ByVal id As Integer)



        Return 50%


    End Function
    Public Function vonProjektzuAngebotenePT(ByVal id As Integer)
        Try
            Dim var = ((From it In _db.Projekts _
                        Select it.Angebots).Last).ToList

            Return var(0).angebotenePT
        Catch
            Return _alternativstring
        End Try


    End Function

    Public Sub ASPNET_MsgBox(ByVal Message As String)

        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">" & vbCrLf)

        System.Web.HttpContext.Current.Response.Write("alert(""" & Message & """)" & vbCrLf)

        System.Web.HttpContext.Current.Response.Write("</SCRIPT>")

    End Sub
    Public Function PTinStudenformat(ByVal pt As String) ' pt in stunden umwandeln
        
        Return Double.Parse(pt) * 8
    End Function

    Public Sub New()
        _db = New hc_databaseDataContext

        _alternativstring = "TBC"
    End Sub

    Public Function gibSollumfangProjekt(ByVal ProjektId As Integer) As Double
        _db = New hc_databaseDataContext
        Dim var = From it In _db.Phase _
                  Where it.Projekt_ID = ProjektId And it.Dauer.HasValue _
                    Select it.Dauer
        Try
            Dim summe As Double
            For Each d As Double In var
                summe = summe + d
            Next
            Dim e As String = Math.Round(summe / 8, 2)
            Return e
        Catch
            Dim b As Double
            b = 0
            Return b
        End Try
    End Function
    ' hier muss noch das angenommen durch PL rein
    Public Function gibIstumfangProjekt(ByVal ProjektId As Integer) As Double
        _db = New hc_databaseDataContext
        Dim var = From it In _db.Reports _
                  Where it.Phase.Projekt_ID = ProjektId And it.Dauer.HasValue _
                  And it.Angenommen_pl = True _
                    Select it.Dauer
        Try
            Dim summe As Double
            For Each d As Double In var
                summe = summe + d
            Next
            Dim e As String = Math.Round(summe / 8, 2)
            Return e
        Catch
            Dim b As Double
            b = 0
            Return b
        End Try
    End Function

    Public Function aktuellerProjektstandPl(ByVal prjekId As Integer) As String
        Try
            Dim var = (From it In _db.Stands _
                      Where it.Projekt_ID = prjekId _
                      Order By it.Datum _
                      Select it.Projektstandbesch).ToList

            Return var.Last
        Catch
            Return "Noch nicht vorhanden"

        End Try
    End Function

    Public Function geplanterProjektabschluss(ByVal projektidd As Integer) As String
        Dim var = (From it In _db.Projekts _
                    Where it.ID = projektidd _
                    Select it.Abschluss_geplant).SingleOrDefault
        If var = Nothing Then
            Return "Angebot noch nicht angenommen"
        Else
            Return var.ToString
        End If

    End Function

    Public Function gibProjekt(ByVal projektid As Integer) As Projekt
        Dim var = (From it In _db.Projekts _
                  Where it.ID = projektid _
                  Select it).Single
        Return var
    End Function
    Public Sub update_geleistetprojekte()
        Dim db As New hc_databaseDataContext
        Dim var9 = From it In db.Projekte_geleistet Select it
        db.Projekte_geleistet.DeleteAllOnSubmit(var9)
        db.SubmitChanges()
        Dim var = From it In db.Projekts _
                  Select it.ID
        For Each d As Integer In var
            Dim var2 = (From it In db.Phase _
                      Where it.Projekt_ID = d _
                      Select it.Dauer).Sum

            Dim var3 = (From it In db.Projekts _
                      Where it.ID = d _
                      Select it).Single
            Dim neuegeleistet As New Projekte_geleistet
            neuegeleistet.projektid = var3.ID
            neuegeleistet.angeboten = var2 / 8
            db.Projekte_geleistet.InsertOnSubmit(neuegeleistet)
            db.SubmitChanges()
        Next


    End Sub
End Class
